CREATE TABLE [dbo].[tb_Auditoria]
(
[idAuditoria] [int] NOT NULL IDENTITY(1, 1),
[auditoria] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Evento] [int] NOT NULL,
[TablaOSP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Campo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccionAuditoria] [int] NOT NULL,
[desde] [datetime] NOT NULL,
[hasta] [datetime] NULL,
[baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[GeneraEvento] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Destinatario] [int] NULL,
[Severidad] [int] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[campo_restriccion] ON [dbo].[tb_Auditoria] 
FOR UPDATE AS

if update (campo) begin
if not exists(Select inserted.* from inserted, syscolumns where syscolumns.id = object_id(tablaOSP) and syscolumns.name = campo) 
	begin
		RAISERROR ('El nombre del campo no coincide con ninguno de los campos de la Tabla especificada', 16, 5)	
		ROLLBACK
	end
end
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[campo_restriccion_in] ON [dbo].[tb_Auditoria] 
FOR INSERT AS

if ((Select campo from inserted) not in 
	(Select syscolumns.name from inserted, syscolumns 
	where syscolumns.id = object_id(inserted.tablaOSP)) and update(campo))
	
	begin
		RAISERROR ('El nombre del campo no coincide con ninguno de los campos de la Tabla especificada', 16, 5)	
		ROLLBACK
	end
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[delAuditoria] ON [dbo].[tb_Auditoria]
FOR DELETE 
AS

delete tb_RegistroAuditoria from tb_RegistroAuditoria where tb_RegistroAuditoria.idAuditoria in (Select idAuditoria from deleted)
delete tb_Auditoria from tb_Auditoria where auditoria = (Select auditoria from deleted)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
	APROVECHA LA MISMA FUNCIONALIDAD DE TB_AUDITURIA PARA GUARDAR
			TODO EL REGISTRO DE UNA TABLA 
*/
CREATE TRIGGER [dbo].[Registro] ON [dbo].[tb_Auditoria] 
FOR INSERT
AS

declare @Accion int    --1: Nada  2: Campo  3: Registro
declare @Tabla varchar(50)
declare @Evento int  
--declare @desde datetime
--declare @hasta datetime
declare @DescrAuditoria varchar(50)
declare @DescrColumna varchar(50)
declare @idAuditoria int
declare @qry as varchar(255)


select @idAuditoria=i.idAuditoria,@Accion=i.accionauditoria,@Tabla=i.TablaoSP,@evento=i.evento,@DescrAuditoria=i.Auditoria,@DescrColumna=i.Campo
from inserted i

if  @evento in (1,2,3)
begin

if @evento= 1 select  @qry='create trigger aud_Alta_'+@Tabla+' on '+@tabla+' for  insert as '
else if @evento=2  select @qry='create trigger  aud_Mod_'+@Tabla+' on '+@tabla+' for update as '
else if @evento=3  select @qry='create trigger  aud_Baja_'+@Tabla+' on '+@tabla+' for delete as '
else select @qry=''


select @qry=@qry+'insert tb_RegistroAuditoria(idAuditoria,fecha,idusuario,descripcion) values('
+convert(varchar(10),@idAuditoria)+',convert(varchar(12),getdate(),103),dbo.sp_usuario(system_user),'''
+@descrauditoria+ ''')'

if @accion=2 and @DescrColumna<>null
begin
    select @qry=@qry+'    select @ultid=max(idDetalleAuditoria) from tb_DetalleAuditoria  insert tb_DetalleAuditoria (idRegistroAuditoria,campo,valor) values (@ultid,'+@descrColumna+',deleted.'+@descrColumna+')'
end
-- falta guardar todo el registro recorriendo syscolumns (como abajo), en caso de que accion=3




exec (@qry)

end

--RAISERROR (@qry, 16, 5)	
--ROLLBACK	

--raiserror  50000,@qry
--end






/*
declare @idaccion int
declare @Tabla varchar(50)
declare @idEvento int
declare @desde datetime
declare @hasta datetime
declare @auditoria varchar(50)
declare @columna varchar(50)

declare @cod int

declare temporal cursor for
Select AccionAuditoria, TablaOSP, Evento, desde, hasta, auditoria from inserted where AccionAuditoria = 3
open temporal

while @@fetch_status = 0 begin
	fetch next from temporal
	into @idAccion, @Tabla, @idEvento, @desde, @hasta, @auditoria
	if (@idAccion = 3) begin
		declare columnas cursor for
		Select syscolumns.name from syscolumns  where id = object_id(@Tabla)
		open columnas
		fetch next from columnas
		into @columna
		while @@fetch_status = 0 begin
			select @cod = (Select max(idAuditoria) from tb_Auditoria)
			select @cod = @cod +1		
			insert into tb_Auditoria (idAuditoria, auditoria, Evento, TablaOSP, campo, AccionAuditoria, desde, hasta, Baja)
			values (@cod, @auditoria, @idEvento, @Tabla, @columna, @idaccion, @desde, @hasta, 'N')
			fetch next from columnas
			into @columna
		end
	end
	fetch next from temporal
	into @idAccion, @Tabla, @idEvento, @desde, @hasta, @auditoria
end

*/







GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[tablaOSP_restriccion] ON [dbo].[tb_Auditoria] 
FOR INSERT
AS
if ((Select tablaOSP from inserted) not in (Select name from sysobjects where xtype='P' or xtype='U'))
	begin
	RAISERROR ('El nombre de la Tabla o Procedimiento no coinciden con alguno existente', 16, 5)	
	ROLLBACK	
	end
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[tablaOSP_restriccion_UP] ON [dbo].[tb_Auditoria] 
FOR UPDATE
AS

-- Si la tabla no existe
if  not exists(Select * from inserted, sysobjects where sysobjects.id = object_id(tablaOSP)) and update(tablaOSP)
 begin	
	 RAISERROR ('El nombre de la Tabla o Procedimiento no coinciden con alguno existente', 16, 5)	
	 ROLLBACK
 end
else
 begin	--si se midifica la tabla y el campo no existe
	if not exists(Select campo from inserted, syscolumns where syscolumns.id = object_id(tablaOSP)) and update(tablaOSP)
	begin
		declare @x int
		Select @x = (Select idAuditoria from inserted)
		update tb_Auditoria
		set campo = null
		where auditoria = @x
	end
 end
GO
ALTER TABLE [dbo].[tb_Auditoria] ADD CONSTRAINT [PK_tb_Auditoria] PRIMARY KEY NONCLUSTERED  ([idAuditoria]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [key1] ON [dbo].[tb_Auditoria] ([TablaOSP], [Campo], [AccionAuditoria]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Auditoria] WITH NOCHECK ADD CONSTRAINT [FK_tb_Auditoria_tb_AccionesAuditoria] FOREIGN KEY ([AccionAuditoria]) REFERENCES [dbo].[tb_AccionesAuditoria] ([idAccionesAuditoria]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_Auditoria] WITH NOCHECK ADD CONSTRAINT [FK_tb_Auditoria_tb_Usuarios] FOREIGN KEY ([Destinatario]) REFERENCES [dbo].[tb_Usuarios] ([idUsuario])
GO
ALTER TABLE [dbo].[tb_Auditoria] WITH NOCHECK ADD CONSTRAINT [FK_tb_Auditoria_tb_EventosAuditoria1] FOREIGN KEY ([Evento]) REFERENCES [dbo].[tb_EventosAuditoria] ([idEvento]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_Auditoria] NOCHECK CONSTRAINT [FK_tb_Auditoria_tb_AccionesAuditoria]
GO
ALTER TABLE [dbo].[tb_Auditoria] NOCHECK CONSTRAINT [FK_tb_Auditoria_tb_EventosAuditoria1]
GO
